package com.weijian.service.impl;

import com.weijian.common.exception.SqlExecutionException;
import com.weijian.config.ai.prompt.AiPrompt;
import com.weijian.dal.enums.ChartType;
import com.weijian.dto.ai.AnalysisResult;
import com.weijian.dto.ai.ConversationRequestDTO;
import com.weijian.dto.ai.ConversationResponseDTO;
import com.weijian.dto.data.ChartData;
import com.weijian.dto.data.QueryResult;
import com.weijian.dto.data.TableInfo;
import com.weijian.service.AnalysisDataSourceService;
import com.weijian.service.ChartRecommendationService;
import com.weijian.service.ConversationService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.ai.chat.client.ChatClient;
import org.springframework.ai.chat.client.advisor.MessageChatMemoryAdvisor;
import org.springframework.ai.chat.client.advisor.SimpleLoggerAdvisor;
import org.springframework.ai.chat.memory.ChatMemory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.ParameterizedTypeReference;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.time.OffsetDateTime;
import java.util.List;

/**
 * 对话服务实现类
 */
@Slf4j
@Service
public class ConversationServiceImpl implements ConversationService {

    @Autowired
    private ChatClient chatClient;

    @Autowired
    private ChatMemory chatMemory;

    @Autowired
    private AnalysisDataSourceService analysisDataSourceService;

    @Autowired
    private ChartRecommendationService chartRecommendationService;

    @Override
    @Transactional
    public ConversationResponseDTO sendMessage(Long userId, ConversationRequestDTO request) {
        log.debug("发送消息: userId={}, sessionId={}, content={}",
                userId, request.getSessionId(), request.getContent());

        long startTime = System.currentTimeMillis();
        String tableInfo = getTableInfoText();

        //构建输入的提示词
        String prompt = String.format(AiPrompt.DATA_PROMPT_TEMPLATE, tableInfo, request.getContent());

        //请求AI，得到说明文本、生成的SQL、推荐的图表类型
        MessageChatMemoryAdvisor memoryAdvisor = MessageChatMemoryAdvisor.builder(chatMemory).conversationId(String.valueOf(userId)).build();
        AnalysisResult analysisResult = chatClient.prompt()
                .user(prompt)
                .advisors(memoryAdvisor)
                .advisors(new SimpleLoggerAdvisor())
                .call()
                .entity(new ParameterizedTypeReference<AnalysisResult>() {
                });
        log.info("Analysis result - sql: {}, recommendedCharts: {}",
                analysisResult.getSql(), analysisResult.getRecommendedCharts());

        String sql = analysisResult.getSql();
        List<ChartType> recommendedCharts = analysisResult.getRecommendedCharts();


        QueryResult queryResult = null;
        ChartData chartData = null;
        String sqlStatus = "NOT_EXECUTED";
        String sqlError = null;

        //执行SQL以及构建返回，包含数据与图表类型
        if (sql != null && !sql.trim().isEmpty()) {
            try {
                log.debug("Executing SQL query: {}", sql);
                queryResult = analysisDataSourceService.executeQuery(sql);
                log.info("SQL executed successfully, rows: {}, execution time: {} ms",
                        queryResult.getRowCount(), queryResult.getExecutionTime());

                // 构建图表数据，传入AI推荐的图表类型
                chartData = chartRecommendationService.buildChartData(queryResult, recommendedCharts);
                log.info("Chart recommendation: {}, alternatives: {}",
                        chartData.getRecommendedChart(), chartData.getAlternativeCharts());

                sqlStatus = "SUCCESS";
            } catch (SqlExecutionException e) {
                log.error("SQL execution failed: {}", sql, e);
                sqlStatus = "FAILED";
                sqlError = e.getMessage();
            }
        }

        long processingTime = System.currentTimeMillis() - startTime;

        // 构建响应
        return ConversationResponseDTO.builder()
                .messageId(userId)
                .content(analysisResult.getResponse())
                .sql(analysisResult.getSql())
                .queryResult(queryResult)
                .chartData(chartData)
                .sqlStatus(sqlStatus)
                .sqlError(sqlError)
                .timestamp(OffsetDateTime.now())
                .processingTime(processingTime)
                .build();
    }

    private String getTableInfoText() {
        try {
            List<TableInfo> tables = analysisDataSourceService.getTables();

            if (tables.isEmpty()) {
                return "暂无可用的数据表";
            }

            StringBuilder sb = new StringBuilder();
            for (TableInfo table : tables) {
                sb.append("表名: ").append(table.getTableName());

                if (table.getTableComment() != null && !table.getTableComment().isEmpty()) {
                    sb.append(" (").append(table.getTableComment()).append(")");
                }

                sb.append("\n");

                // 获取表结构详情
                try {
                    var structure = analysisDataSourceService.getTableStructure(table.getTableName());
                    sb.append("列: ");
                    sb.append(String.join(", ",
                            structure.getColumns().stream()
                                    .map(col -> col.getColumnName() + " (" + col.getDataType() + ")")
                                    .toList()
                    ));
                    sb.append("\n");
                } catch (Exception e) {
                    log.warn("Failed to get structure for table: {}", table.getTableName(), e);
                }

                sb.append("\n");
            }

            return sb.toString();
        } catch (Exception e) {
            log.error("Failed to get table information", e);
            return "无法获取表信息";
        }
    }
}
